package com.kcn.xml;

import java.io.File;
import java.io.IOException;
import java.text.ParseException;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Date;
import java.util.List;

import jxl.Sheet;
import jxl.Workbook;
import jxl.read.biff.BiffException;

import com.kcn.utils.PropertiesReader;

public class ExcelReader {
	private String month;

	public String getMonth() {
		return month;
	}

	public void setMonth(String month) {
		this.month = month;
	}

	public String getYear() {
		return year;
	}

	public void setYear(String year) {
		this.year = year;
	}

	private String year;
	private List<Invoice> invoices = new ArrayList<Invoice>();

	public List<Invoice> read(String filename) throws BiffException,
			IOException {
		String filepath = PropertiesReader.getPropertiesReader().getProperty(
				"uploadPath")
				+ filename;

		try {
			File fl = new File(filepath);
			System.out.println(fl.getName());
			Workbook wb = Workbook.getWorkbook(fl);
			Sheet st = wb.getSheet(0);
			this.setMonth(st.getCell(1, 3).getContents());
			this.setYear(st.getCell(1, 1).getContents());
			System.out.println("年份：" + this.getYear());
			System.out.println("月份：" + this.getMonth());
			System.out.println(st.getRows());
			for (int i = 7; i < st.getRows(); i++) {
				Invoice iv = new Invoice();
				iv.setFpdm(st.getCell(3, i).getContents());
				// 固定名称
				iv.setFpmc("凯诺斯铝酸盐发票");
				iv.setFphm(st.getCell(2, i).getContents());
				iv.setKprq(dateformat(st.getCell(7, i).getContents()));
				// 固定为凯诺斯法国
				iv.setFkfmc("凯诺斯法国");
				iv.setJehj(st.getCell(8, i).getContents());
				iv.setKplx(st.getCell(4, i).getContents());
				// 原发票代码
				iv.setYfpdm("");
				// 原发票号码
				iv.setYfphm("");
				// 产品品目
				iv.setPm(st.getCell(13, i).getContents());
				iv.setDw(st.getCell(15, i).getContents());
				iv.setDj(st.getCell(16, i).getContents());
				iv.setSl(st.getCell(14, i).getContents());
				iv.setJe(st.getCell(17, i).getContents());
				invoices.add(iv);
			}
			System.out.println("实际导入总发票行：" + invoices.size());
			System.out.println("BO发票行：" + st.getCell(11, 6).getContents());
			wb.close();
		} catch (BiffException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		return invoices;
	}

	public Date dateformat(String date) {
		SimpleDateFormat sdf = new SimpleDateFormat("dd/MM/yy");
		Date kprq = null;
		if (date != "") {
			try {
				kprq = sdf.parse(date);
			} catch (ParseException e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			}
		}
		return kprq;
	}
}
